package com.gs.mp.db.utils;

import com.google.common.collect.Lists;
import com.gs.common.tool.consts.TagConst;
import com.gs.common.tool.pojo.ColumnInfo;
import com.gs.common.tool.pojo.TableInfo;
import com.gs.mp.db.config.JdbcConnection;

import java.io.File;
import java.net.URL;
import java.net.URLClassLoader;
import java.sql.*;
import java.util.List;
import java.util.Objects;
import java.util.Properties;
import java.util.concurrent.atomic.AtomicInteger;

/**
 * 数据库工具类
 *
 * @author: guoshuai
 * @date: 2024/07/12 09:49
 **/
public class DbUtil {


    /**
     * 获取jdbc连接
     *
     * @param jdbcConnection 连接对象
     * @date 2024/07/12 10:30
     **/
    public static Connection getConnection(String jdbcJarPath, JdbcConnection jdbcConnection) {
        if (Objects.isNull(jdbcConnection)) {
            throw new IllegalArgumentException(TagConst.jdbcConnection + " element tag is not null!");
        }
        try {
            //指定驱动所在的路径,创建URLClassLoader
            URLClassLoader classLoader = new URLClassLoader(new URL[]{new File(jdbcJarPath).toURI().toURL()}, Thread.currentThread().getContextClassLoader());
            Driver driver = (Driver) Class.forName(jdbcConnection.getDriverClass(), true, classLoader).newInstance();
            Properties props = new Properties();
            props.setProperty("user", jdbcConnection.getUserId());
            props.setProperty("password", jdbcConnection.getPassword());
            return driver.connect(jdbcConnection.getConnectionURL(), props);
        } catch (Exception e) {
            e.printStackTrace();
            throw new IllegalArgumentException(e.getMessage());
        }
    }


    /**
     * 关闭jdbc连接
     *
     * @param connection 连接
     * @date 2024/07/12 10:33
     **/
    public static void closeConnection(Connection connection) {
        if (Objects.isNull(connection)) {
            return;
        }
        try {
            connection.close();
        } catch (SQLException e) {
            // ignore
        }
    }


    /**
     * 根据表名获取mysql表信息
     *
     * @param connection 连接
     * @param tableName  表名
     * @date 2024/07/12 11:02
     **/
    public static TableInfo getMysqlTableInfo(Connection connection, String scheme, String tableName) {
        StringBuilder querySql = new StringBuilder("select t.* from (");
        querySql.append("SELECT TABLE_NAME as name,'table' as type,TABLE_COMMENT as comment,0 as is_pk,-1 as sort FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA =? and TABLE_NAME=?");
        querySql.append(" union");
        querySql.append(" SELECT `COLUMN_NAME` as name,`DATA_TYPE` as type,`COLUMN_COMMENT` AS comment,if(COLUMN_KEY='PRI',1,0) AS is_pk,ordinal_position AS sort  FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA =? and TABLE_NAME = ?");
        querySql.append(" ) t order by t.sort");
        try {
            PreparedStatement pst = connection.prepareStatement(querySql.toString());
            pst.setString(1, scheme);
            pst.setString(2, tableName);
            pst.setString(3, scheme);
            pst.setString(4, tableName);
            ResultSet rs = pst.executeQuery();
            return assembleTableInfo(tableName, rs);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return new TableInfo();
    }

    /**
     * 根据表名获取postgresql表信息
     *
     * @param connection 连接
     * @param tableName  表名
     * @date 2024/07/12 11:02
     **/
    public static TableInfo getPgTableInfo(Connection connection, String scheme, String tableName) {
        TableInfo tableInfo = new TableInfo();
        StringBuilder querySql = new StringBuilder("select t.* from (");
        querySql.append(" SELECT tb.table_name as name,'table' as type,d.description as comment,0 as is_pk,0 as sort FROM information_schema.tables tb JOIN pg_class c ON c.relname = tb.table_name LEFT JOIN pg_description d ON d.objoid = c.oid AND d.objsubid = '0' WHERE tb.table_schema = ? AND tb.table_name = ?");
        querySql.append(" union");
        querySql.append(" SELECT a.attname as name,format_type(a.atttypid,a.atttypmod) as type,d.description as comment,if(pk.column_name=a.attname,1,0) AS is_pk,a.attnum as sort FROM pg_attribute a LEFT JOIN pg_description d ON a.attrelid = d.objoid AND a.attnum = d.objsubid");
        querySql.append(" LEFT JOIN (SELECT kcu.column_name FROM information_schema.table_constraints tc JOIN information_schema.key_column_usage kcu ON tc.constraint_name = kcu.constraint_name AND tc.table_schema = kcu.table_schema WHERE tc.constraint_type = 'PRIMARY KEY' AND tc.table_schema = ? AND tc.table_name = ?) pk ON a.attname=pk.column_name");
        querySql.append(" WHERE a.attnum > 0 and a.attrelid = ?::regclass AND NOT a.attisdropped");
        querySql.append(" ) t order by t.sort");
        try {
            PreparedStatement pst = connection.prepareStatement(querySql.toString());
            pst.setString(1, scheme);
            pst.setString(2, tableName);
            pst.setString(3, scheme);
            pst.setString(4, tableName);
            pst.setString(5, String.format("%s.%s", scheme, tableName));
            ResultSet rs = pst.executeQuery();
            return assembleTableInfo(tableName, rs);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return tableInfo;
    }


    /**
     * 根据表名获取sqlSever表信息
     *
     * @param connection 连接
     * @param tableName  表名
     * @date 2024/07/12 11:02
     **/
    public static TableInfo getSqlSeverTableInfo(Connection connection, String scheme, String tableName) {
        TableInfo tableInfo = new TableInfo();
        StringBuilder querySql = new StringBuilder("select t.* from (");
        querySql.append(" SELECT OBJECT_NAME(major_id) AS name,'table' AS type,value AS comment,0 AS is_pk,0 AS sort FROM sys.extended_properties WHERE major_id = OBJECT_ID(?) AND minor_id = 0");
        querySql.append(" union");
        querySql.append(" SELECT c.name AS name,t.name AS type,p.value AS comment,CASE WHEN pk.column_name = c.name THEN 1 ELSE 0 END AS is_pk,c.column_id AS sort FROM sys.columns c JOIN sys.types t ON c.system_type_id = t.system_type_id LEFT JOIN sys.extended_properties p ON c.object_id = p.major_id AND c.column_id = p.minor_id");
        querySql.append(" LEFT JOIN (SELECT column_name FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE table_name=?) pk ON c.name=pk.column_name");
        querySql.append(" WHERE t.name !='sysname' AND c.object_id = OBJECT_ID(?)");
        querySql.append(" ) t order by t.sort");
        try {
            PreparedStatement pst = connection.prepareStatement(querySql.toString());
            pst.setString(1, String.format("%s.%s", scheme, tableName));
            pst.setString(2, String.format("%s.%s", scheme, tableName));
            pst.setString(3, String.format("%s.%s", scheme, tableName));
            ResultSet rs = pst.executeQuery();
            return assembleTableInfo(tableName, rs);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return tableInfo;
    }


    private static TableInfo assembleTableInfo(String tableName, ResultSet rs) throws SQLException {
        TableInfo tableInfo = new TableInfo();
        tableInfo.setName(tableName);
        List<ColumnInfo> columnInfoList = Lists.newArrayList();
        tableInfo.setColumnInfoList(columnInfoList);
        AtomicInteger rowNum = new AtomicInteger(0);
        while (rs.next()) {
            if ("table".equals(rs.getString("type"))) {
                tableInfo.setComment(rs.getString("comment"));
            } else {
                ColumnInfo columnInfo = new ColumnInfo();
                columnInfo.setColumnName(rs.getString("name"));
                columnInfo.setColumnType(rs.getString("type"));
                columnInfo.setPK(Objects.equals(1, rs.getInt("is_pk")));
                columnInfo.setColumnComment(rs.getString("comment"));
                columnInfo.setIndex(rowNum.incrementAndGet());
                columnInfoList.add(columnInfo);
            }
        }
        return tableInfo;
    }
}
